import sqlite3
from sqlite3 import Connection, Cursor

# 定义一个数据库连接类
class DatabaseDriver:
    # 初始化
    def __init__(self, user_name: str = "", user_password: str = ""):
        self.connection = False
        if user_name == "" and user_password == "":
            self.conn: Connection = sqlite3.connect("./db/ordering_system.db")
            self.connection = True

    def update(self, sql: str, log: bool = False):
        if self.connection:
            cursor: Cursor = self.conn.cursor()
            cursor.execute(sql)
            if log:
                print("sql execute: {}".format(sql))
                print("sql update count: {}".format(self.conn.total_changes))
            self.conn.commit()
            return self.conn.total_changes
        else:
            raise Exception("数据库连接已关闭！")

    def insert(self, sql: str, log: bool = False):
        return self.update(sql, log)

    def delete(self, sql: str, log: bool = False):
        return self.update(sql, log)

    def select_all(self, sql: str, log: bool = False):
        if self.connection:
            cursor = self.conn.cursor()
            if log:
                print("sql execute: {}".format(sql))
            cursor.execute(sql)
            self.conn.commit()
            data = cursor.fetchall()
            if log:
                print("sql execute result lengths: {}".format(len(data)))
                for index, d in enumerate(data):
                    print("sql execute result{}: {}".format(index + 1, d))
            return data
        else:
            raise Exception("数据库连接已关闭！")

    def select_one(self, sql: str, log: bool = False):
        if self.connection:
            cursor = self.conn.cursor()
            if log:
                print("sql execute: {}".format(sql))
            cursor.execute(sql)
            self.conn.commit()
            data = cursor.fetchone()
            if log:
                print("sql execute result: {}".format(data))
            return data
        else:
            raise Exception("数据库连接已关闭！")

    def close(self):
        if self.connection:
            self.conn.close()
            self.connection = False

    def execute(self, sql: str, log: bool = False) -> int:
        try:
            # 获取数据库游标
            cursor = self.conn.cursor()
            cursor.execute(sql)
            # 获取受影响的行数
            affected_rows = cursor.rowcount
            self.conn.commit()
            # 如果log为True，打印执行的SQL语句
            if log:
                print(f"Executed SQL: {sql}")
            return affected_rows
        # 如果执行过程中发生异常，打印错误信息并回滚事务
        except Exception as e:
            print(f"Error executing SQL: {e}")
            self.conn.rollback()
            return 0
        finally:
            cursor.close()

if __name__ == '__main__':
    db = DatabaseDriver()
    db.select_all(sql="SELECT * FROM user WHERE 1 = 1", log=True)